package com.quik.test;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.ArrayUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.DateUtil;

public class ImportFromExcel {

	/**
	 * @param args
	 */
	private static Logger log = Logger.getLogger(ImportFromExcel.class);
	private static boolean doDelete = true;
	private static boolean doRollback = false;
	private static Map<String, Integer[]> dataIdMap = new HashMap<String, Integer[]>();

	public static void main(String[] args) throws Exception {
		List<DataBySheet> dataList = parseExcel("D:/Ray/aaa.xls");
		String url = "jdbc:mysql://localhost:3306/quik?useUnicode=true&characterEncoding=utf-8";
		String username = "root";
		String password = "mysql,123";
		Class.forName("org.gjt.mm.mysql.Driver");
		Connection con = DriverManager.getConnection(url, username, password);
		con.setAutoCommit(false);
		try {
			for (DataBySheet data : dataList) {
				if (data.tableName == null || "".equals(data.tableName.trim())) {
					log.info("No table name " + data.tableName);
					continue;
				}
				if (data.tableName.startsWith("#")) {
					log.info("Ignore " + data.tableName);
					continue;
				}
				if (doDelete) {
					String sql = "delete from " + data.tableName;
					log.info("=SQL=" + sql);
					PreparedStatement ps = con.prepareStatement(sql);
					ps.execute();
				}
				log.info(data);
				String sql = generateSql(data.tableName, data.fieldList);
				log.info("=SQL=" + sql);
				executePreparedStatement(con, sql, data.values);
			}
			if (doRollback)
				con.rollback();
			else
				con.commit();
			con.close();
		} catch (Exception e) {
			e.printStackTrace();
			con.rollback();
		}
	}

	static void test1() {

		DataBySheet s = new DataBySheet();
		s.tableName = "sys_account";
		s.fieldList = new String[] { "username", "password" };
		List<Object[]> list = new ArrayList<Object[]>();
		list.add(new Object[] { 10, 20, 30 });
		list.add(new Object[] { "a", "b", "c" });
		list.add(new Object[] { 10, "a", 'b' });
		s.values = list;
		log.info(s);

	}

	static List<DataBySheet> parseExcel(String filepath) throws IOException {
		FileInputStream in = new FileInputStream(filepath);
		HSSFWorkbook wb = new HSSFWorkbook(in);
		int numOfSheets = wb.getNumberOfSheets();
		List<DataBySheet> rs = new ArrayList<ImportFromExcel.DataBySheet>(
				numOfSheets);
		for (int i = 0; i < numOfSheets; i++) {
			HSSFSheet sheet = wb.getSheetAt(i);
			DataBySheet dataBySheet = parseDataBySheet(sheet);
			rs.add(dataBySheet);
		}
		return rs;
	}

	static DataBySheet parseDataBySheet(HSSFSheet sheet) {
		DataBySheet rs = new DataBySheet();
		rs.tableName = sheet.getSheetName();
		if (rs.tableName.startsWith("#")) {
			return rs;
		}
		int firstRowNum = sheet.getFirstRowNum();
		int lastRowNum = sheet.getLastRowNum();
		HSSFRow fieldSetRow = sheet.getRow(firstRowNum);
		int firstCellNum = fieldSetRow.getFirstCellNum();
		int lastCellNum = fieldSetRow.getLastCellNum();
		List<String> fieldList = new ArrayList<String>(lastCellNum + 1
				- firstCellNum);
		for (int i = firstCellNum; i < lastCellNum; i++) {
			log.info(rs.tableName + "," + i);
			fieldList.add(fieldSetRow.getCell(i).getStringCellValue());
		}
		rs.fieldList = fieldList.toArray(new String[0]);
		List<Object[]> values = new ArrayList<Object[]>(lastRowNum + 1
				- firstRowNum);
		for (int i = firstRowNum + 1; i <= lastRowNum; i++) {
			values.add(parseDataByRow(sheet.getRow(i), firstCellNum,
					lastCellNum));

		}
		rs.values = values;
		return rs;
	}

	static Object[] parseDataByRow(HSSFRow row, int firstCellNum,
			int lastCellNum) {
		List<Object> rs = new ArrayList<Object>(lastCellNum + 1 - firstCellNum);
		for (int i = firstCellNum; i < lastCellNum; i++) {
			HSSFCell cell = row.getCell(i);
			if (cell == null) {
				rs.add("");
			} else if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
				rs.add("");
			} else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC
					&& DateUtil.isCellDateFormatted(cell)) {
				rs.add(cell.getDateCellValue());
			} else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
				rs.add(cell.getNumericCellValue());
			} else {
				rs.add(cell.getStringCellValue());
			}
		}
		return rs.toArray();

	}

	static void executePreparedStatement(Connection con, String sql,
			List<Object[]> values) throws SQLException {
		PreparedStatement ps = con.prepareStatement(sql);
		for (int i = 0; i < values.size(); i++) {
			setUpParameter(ps, values.get(i));
			ps.addBatch();
		}
		ps.executeBatch();
		ps.close();
	}

	static void setUpParameter(PreparedStatement ps, Object[] values)
			throws SQLException {
		for (int i = 0; i < values.length; i++) {
			ps.setObject(i + 1, values[i]);
		}
	}

	static String generateSql(String tableName, String[] fieldList) {
		StringBuilder builder = new StringBuilder("insert into ");
		builder.append(tableName).append("(");
		boolean isFirst = true;
		for (Object field : fieldList) {
			if (isFirst) {
				isFirst = false;
			} else {
				builder.append(",");
			}
			builder.append(field);
		}
		builder.append(") values (");
		isFirst = true;
		for (int i = 0; i < fieldList.length; i++) {
			if (isFirst) {
				isFirst = false;
			} else {
				builder.append(",");
			}
			builder.append("?");
		}
		builder.append(")");
		return builder.toString();
	}

	static class DataBySheet {
		String tableName;
		String[] fieldList;
		List<Object[]> values;

		public String toString() {
			StringBuilder builder = new StringBuilder();
			builder.append("TABLE-NAME:").append(tableName);
			builder.append(ArrayUtils.toString(fieldList)).append("\n");
			if (values != null)
				for (Object[] objs : values) {
					builder.append(ArrayUtils.toString(objs)).append("\n");
				}
			return builder.toString();
		}
	}
}
